import asyncio
import inspect
import sqlite3
from typing import Optional, List, Dict, Any, Tuple
from aiohttp import web
from ..core.database.connection import db_manager
from ..core.database.schema import CharacterCreate, CharacterUpdate
from pydantic import BaseModel, validator
import os
import traceback
import aiofiles.os as aio_os
import aiofiles
from PIL import Image
from io import BytesIO
from urllib.parse import unquote
from pathlib import Path
from .utils.task_manager import task_manager


# 数据模型验证
class RatingRange(BaseModel):
    rating: Optional[List[float]] = None

    @validator('rating')
    def check_rating_range(cls, v):
        if v is not None:
            if len(v) != 2:
                raise ValueError('评分范围必须是包含两个元素的数组')
            if not all(0 <= x <= 10 for x in v):
                raise ValueError('评分值必须是0-10之间的数字')
        return v


class AdjacentRequest(BaseModel):
    current_name: str
    direction: str
    rating: Optional[List[float]] = None

    @validator('direction')
    def check_direction(cls, v):
        if v not in ['prev', 'next']:
            raise ValueError('方向必须是 prev 或 next')
        return v


# 通用工具函数
def validate_table_name(table: str) -> bool:
    """验证表名安全性"""
    allowed_tables = {"character", "action", "cloth", "collect"}
    return table in allowed_tables


def dict_from_row(row: sqlite3.Row) -> Dict[str, Any]:
    """将sqlite3.Row转换为字典"""
    if row is None:
        return None
    return {key: row[key] for key in row.keys()}


# 业务逻辑服务
class CharacterService:
    IMAGE_EXTENSIONS = {'.jpg', '.jpeg', '.png', '.gif', '.bmp', '.webp'}
    TEXT_EXTENSIONS = {'.txt'}

    @staticmethod
    async def get_random_character(rating: Optional[List[float]]) -> Dict[str, Any]:
        """获取随机角色"""
        query = 'SELECT * FROM character'
        conditions: List[str] = []
        params: List[Any] = []

        # 添加评分范围条件
        if isinstance(rating, list) and len(rating) == 2:
            min_rating, max_rating = rating
            if not isinstance(min_rating, float) or not isinstance(max_rating, float):
                raise web.HTTPBadRequest(reason="评分值必须是数字")

            if min_rating >= 0:
                conditions.append('rating >= ?')
                params.append(min_rating)
            if max_rating <= 10:
                conditions.append('rating <= ?')
                params.append(max_rating)

        # 组合查询条件
        if conditions:
            query += ' WHERE ' + ' AND '.join(conditions)

        # 添加随机排序和限制
        query += ' ORDER BY RANDOM() LIMIT 1'

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute(query, params)
            result = cursor.fetchone()

            if not result:
                return {"result": None, "message": "未找到匹配的人物"}

            return {"result": dict_from_row(result)}

    @staticmethod
    async def get_adjacent_character(current_name: str, direction: str, rating: Optional[List[float]]) -> Dict[
        str, Any]:
        """获取相邻角色"""
        # 特殊处理：当current_name为空字符串时，返回第一条记录
        if current_name == '':
            conditions: List[str] = []
            params: List[Any] = []

            # 添加评分范围条件
            if isinstance(rating, list) and len(rating) == 2:
                min_rating, max_rating = rating
                if min_rating >= 0:
                    conditions.append('rating >= ?')
                    params.append(min_rating)
                if max_rating <= 10:
                    conditions.append('rating <= ?')
                    params.append(max_rating)

            # 构建查询
            query = 'SELECT * FROM character'
            if conditions:
                query += ' WHERE ' + ' AND '.join(conditions)
            query += ' ORDER BY name ASC LIMIT 1'

            with db_manager.get_connection() as conn:
                conn.row_factory = sqlite3.Row
                result = conn.execute(query, params).fetchone()
                return {"result": dict_from_row(result) or None}

        # 获取当前记录信息
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            current_record = conn.execute(
                'SELECT name, rating FROM character WHERE name = ?',
                [current_name]
            ).fetchone()

            if not current_record:
                raise web.HTTPNotFound(reason="当前记录不存在")

            name, current_rating = current_record['name'], current_record['rating']

            # 构建查询参数
            adjacent_query = ''
            conditions: List[str] = []
            params: List[Any] = []

            # 添加评分范围条件
            if isinstance(rating, list) and len(rating) == 2:
                min_rating, max_rating = rating
                if min_rating >= 0:
                    conditions.append('rating >= ?')
                    params.append(min_rating)
                if max_rating <= 10:
                    conditions.append('rating <= ?')
                    params.append(max_rating)

            # 添加方向条件
            if direction == 'prev':
                conditions.append('name < ?')
                params.append(name)
                adjacent_query = f"""
                    SELECT * FROM character
                    {'WHERE ' + ' AND '.join(conditions) if conditions else ''}
                    ORDER BY name DESC
                    LIMIT 1
                """
            else:  # 'next'
                conditions.append('name > ?')
                params.append(name)
                adjacent_query = f"""
                    SELECT * FROM character
                    {'WHERE ' + ' AND '.join(conditions) if conditions else ''}
                    ORDER BY name ASC
                    LIMIT 1
                """

            # 执行相邻记录查询
            adjacent_item = conn.execute(adjacent_query, params).fetchone()

            # 边界处理：无相邻记录时返回第一条
            if not adjacent_item:
                first_item_query = 'SELECT * FROM character'
                if conditions:
                    # 移除方向条件，保留评分条件
                    base_conditions = [c for c in conditions if not c.startswith('name')]
                    if base_conditions:
                        first_item_query += ' WHERE ' + ' AND '.join(base_conditions)

                first_item_query += ' ORDER BY name ASC LIMIT 1'

                # 使用评分参数，但不包含方向参数
                base_params = [p for p in params if p != name]
                adjacent_item = conn.execute(first_item_query, base_params).fetchone()

            return {"result": dict_from_row(adjacent_item) or None}

    @staticmethod
    async def delete_character(name: str) -> Dict[str, Any]:
        """删除角色"""
        with db_manager.get_connection() as conn:
            cursor = conn.execute(
                'DELETE FROM character WHERE name = ?',
                [name]
            )

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason="角色不存在")

            return {"result": cursor.rowcount}

    @staticmethod
    async def delete_disliked_character() -> Dict[str, Any]:
        """删除角色"""
        with db_manager.get_connection() as conn:
            cursor = conn.execute(
                'DELETE FROM character WHERE disliked = ?',
                '1'
            )

            if cursor.rowcount == 0:
                raise web.HTTPNotFound(reason="角色不存在")

            return {"result": cursor.rowcount}

    @staticmethod
    async def update_character(data: dict) -> Dict[str, Any]:
        """更新角色信息"""
        name = data.get('name')
        if not name:
            raise web.HTTPBadRequest(reason="必须提供角色名称")

        updates = ['updated_at = CURRENT_TIMESTAMP']  # 自动更新修改时间
        params: List[Any] = []

        # 动态构建更新字段
        field_map = {
            'cn_name': 'cn_name',
            'tag': 'tag',
            'description': 'description',
            'rating': 'rating',
            'favorite': 'favorite',
            'disliked': 'disliked'
        }

        for key, db_field in field_map.items():
            if key in data:
                updates.append(f"{db_field} = ?")

                # 特殊处理布尔值
                if key in ['favorite', 'disliked']:
                    params.append(1 if data[key] else 0)
                else:
                    params.append(data[key])

        if len(updates) <= 1:  # 只包含updated_at
            raise web.HTTPBadRequest(reason="至少需要提供一个有效字段进行修改")

        params.append(name)

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            try:
                cursor = conn.execute(
                    f"UPDATE character SET {', '.join(updates)} WHERE name = ?",
                    params
                )

                if cursor.rowcount == 0:
                    raise web.HTTPNotFound(reason="角色不存在")

                # 获取更新后的完整数据
                updated = conn.execute("""
                    SELECT
                        name, cn_name AS label, tag AS value,
                        description, rating, favorite, disliked,
                        created_at, updated_at
                    FROM character WHERE name = ?
                """, [name]).fetchone()

                return {"data": dict_from_row(updated)}

            except sqlite3.IntegrityError as e:
                if 'CHECK constraint' in str(e):
                    raise web.HTTPBadRequest(reason="评分必须在0-10之间")
                raise web.HTTPConflict(reason=str(e))

    @staticmethod
    async def get_all_characters() -> Dict[str, Any]:
        """获取所有角色"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute('SELECT cn_name, name, tag, rating FROM character')
            data = [dict_from_row(row) for row in cursor.fetchall()]
            return {"data": data}

    @staticmethod
    async def add_character(data: CharacterCreate) -> Dict[str, Any]:
        """添加角色"""
        with db_manager.get_connection() as conn:
            try:
                cursor = conn.execute("""
                    INSERT INTO character (
                        name, cn_name, tag,
                        description, rating, favorite, disliked
                    ) VALUES (?, ?, ?, ?, ?, ?, ?)
                """, [
                    data.name, data.cn_name, data.tag,
                    data.description or None,
                    data.rating if data.rating is not None else 0.0,
                    1 if data.favorite else 0,
                    1 if data.disliked else 0
                ])
                return {"result": cursor.rowcount}
            except sqlite3.IntegrityError as e:
                raise web.HTTPConflict(reason=str(e))

    @staticmethod
    async def get_character_paged(
            page: int,
            page_size: int,
            name: Optional[str],
            tag: Optional[str],
            cn_name: Optional[str],
            description: Optional[str],
            favorite: Optional[str],
            disliked: Optional[str],
            rating_min: Optional[float],
            rating_max: Optional[float],
            sort_field: str,
            sort_order: str
    ) -> Dict[str, Any]:
        """分页查询角色"""
        # 验证排序字段
        allowed_fields = [
            'name', 'cn_name', 'tag', 'created_at',
            'updated_at', 'rating', 'favorite'
        ]
        order_by = sort_field if sort_field in allowed_fields else 'updated_at'
        order_dir = sort_order.upper() if sort_order.upper() in ['ASC', 'DESC'] else 'ASC'

        where: List[str] = []
        params: List[Any] = []

        # 构建查询条件
        if name:
            where.append('name LIKE ?')
            params.append(f'%{name}%')
        if cn_name:
            where.append('cn_name LIKE ?')
            params.append(f'%{cn_name}%')
        if tag:
            where.append('tag = ?')
            params.append(tag)
        if description:
            where.append('description LIKE ?')
            params.append(f'%{description}%')

        if favorite is not None and favorite != '':
            where.append('favorite = ?')
            params.append(favorite)
        if disliked is not None and disliked != '':
            where.append('disliked = ?')
            params.append(disliked)
        if rating_min is not None:
            where.append('rating >= ?')
            params.append(rating_min)
        if rating_max is not None:
            where.append('rating <= ?')
            params.append(rating_max)

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''

        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row

            # 查询数据
            data_cursor = conn.execute(f"""
                SELECT
                    name, cn_name, tag,
                    description, rating, favorite, disliked,
                    created_at, updated_at
                FROM character
                {where_clause}
                ORDER BY {order_by} {order_dir}
                LIMIT ? OFFSET ?
            """, params + [page_size, (page - 1) * page_size])

            data = [dict_from_row(row) for row in data_cursor.fetchall()]

            # 查询总数
            total_cursor = conn.execute(
                f'SELECT COUNT(*) AS total FROM character {where_clause}',
                params
            )
            total = total_cursor.fetchone()['total']

            return {
                "data": data,
                "pagination": {
                    "total": total,
                    "page": page,
                    "page_size": page_size,
                    "total_pages": (total + page_size - 1) // page_size
                }
            }

    @staticmethod
    async def get_character_detail(name: str) -> Dict[str, Any]:
        """获取角色详情"""
        with db_manager.get_connection() as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.execute("""
                SELECT
                    name, cn_name AS label, tag AS value,
                    description, rating, favorite, disliked,
                    created_at, updated_at
                FROM character WHERE name = ?
            """, [name])

            result = cursor.fetchone()
            if not result:
                raise web.HTTPNotFound(reason="角色不存在")

            return {"data": dict_from_row(result)}

    @classmethod
    async def batch_add_character(cls, request: web.Request) -> web.Response:
        """处理多文件上传，处理并批量导入角色数据，带完整进度跟踪"""
        errors = []
        task_id = None
        try:
            data = await request.post()

            # 获取前端传入的任务ID
            task_id = data.get('taskId')
            if not task_id or not isinstance(task_id, str):
                return web.json_response({
                    "success": False,
                    "message": "缺少有效的任务ID（taskId）"
                }, status=400)

            image_files, text_files = cls._separate_files(data)

            # 获取并验证目标路径参数
            target_dir_str = data.get('targetPath')
            if not target_dir_str or not isinstance(target_dir_str, str):
                errors.append("目标路径不存在或无效，请提供有效的targetPath参数")
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的角色数据",
                    "errors": errors
                }, status=400)

            if not image_files:
                errors.append("未找到任何图片文件，请检查上传的文件是否包含支持的图片格式")
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的角色数据",
                    "errors": errors
                }, status=400)

            # 初始化任务进度（总任务数为图片数量 + 1，加1用于数据库插入步骤）
            total_image_tasks = len(image_files)
            total_tasks = total_image_tasks + 1  # 额外加1步数据库插入
            task_manager.create_task(task_id, total_tasks)

            # 注册取消任务的回调
            def cancel_import():
                print(f"任务 {task_id} 被取消")
                # 可以在这里添加资源清理逻辑

            task_manager.add_active_export(task_id, cancel_import)

            if not text_files:
                errors.append("未找到任何文本文件，将使用图片文件名作为tag")

            text_map = {}
            if text_files:
                try:
                    text_map = await cls._process_text_files(text_files)
                except Exception as e:
                    err_msg = f"处理文本文件时出错: {str(e)}"
                    errors.append(err_msg)
                    print(f"文本文件处理错误: {traceback.format_exc()}")

            characters = []
            try:
                # 传递任务ID用于进度更新
                characters = await cls._process_image_files(
                    image_files, text_map, errors, target_dir_str, task_id, total_image_tasks
                )
            except Exception as e:
                err_msg = f"处理图片文件时出错: {str(e)}"
                errors.append(err_msg)
                print(f"图片文件处理错误: {traceback.format_exc()}")
                # 标记任务失败
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": err_msg,
                    "errors": errors
                }, status=500)

            if not characters:
                task_manager.complete_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": "未处理到任何有效的角色数据",
                    "errors": errors
                }, status=400)

            # 图片处理完成后，更新进度到图片数量（接近完成）
            task_manager.update_progress(task_id, total_image_tasks)
            await asyncio.sleep(0.01)  # 给前端一点时间接收进度更新

            # 执行数据库插入并跟踪进度
            try:
                # 可以在这里添加插入前的准备工作进度更新
                result = await cls.bulk_add_characters(characters)

                # 数据库插入完成，更新进度到100%
                task_manager.update_progress(task_id, total_tasks)
                await asyncio.sleep(0.01)
            except Exception as e:
                err_msg = f"数据库插入失败: {str(e)}"
                errors.append(err_msg)
                print(f"数据库插入错误: {traceback.format_exc()}")
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)
                return web.json_response({
                    "success": False,
                    "message": err_msg,
                    "errors": errors
                }, status=500)

            # 标记任务完成
            task_manager.complete_task(task_id)

            return web.json_response({
                "success": True,
                "message": f"成功导入 {result} 个角色",
                "count": result,
                "warnings": errors
            })

        except Exception as e:
            error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
            full_error = traceback.format_exc()
            print(f"批量添加角色失败: {full_error}")

            # 如果有任务ID，标记任务失败
            if task_id:
                await task_manager.emit('error', task_id, e)
                task_manager.cancel_task(task_id)

            return web.json_response({
                "success": False,
                "message": f"处理失败: {error_msg}",
                "errors": errors + [full_error.split('\n')[-2]]
            }, status=400)


    @classmethod
    def _separate_files(cls, data: Dict[str, Any]) -> Tuple[List[Any], List[Any]]:
        """分离图片文件和文本文件，适配不同的文件上传对象"""
        image_files = []
        text_files = []

        # 处理aiohttp的MultiDict格式，确保正确提取文件
        for key, value in data.items():
            # 检查是否是文件字段（可能是单个文件或文件列表）
            if key == 'files':
                # 确保files是列表格式
                files = value if isinstance(value, list) else [value]

                for file in files:
                    if hasattr(file, 'filename') and file.filename:
                        filename = file.filename.lower()
                        ext = os.path.splitext(filename)[1]

                        if ext in cls.IMAGE_EXTENSIONS:
                            image_files.append(file)
                        elif ext in cls.TEXT_EXTENSIONS:
                            text_files.append(file)
                        else:
                            print(f"忽略不支持的文件类型: {filename} (扩展名: {ext})")

        if not image_files and not text_files:
            print("未找到有效的上传文件")

        return image_files, text_files


    @classmethod
    async def _process_text_files(cls, text_files: List[Any]) -> Dict[str, str]:
        """处理文本文件，提取内容并建立文件名到内容的映射"""
        text_map = {}

        for file in text_files:
            try:
                filename = os.path.splitext(file.filename)[0]

                # 区分同步和异步读取
                if hasattr(file, 'file'):
                    # 同步读取（返回bytes，不需要await）
                    content = file.file.read()
                elif hasattr(file, 'read'):
                    # 检查是否为协程函数
                    if inspect.iscoroutinefunction(file.read):
                        content = await file.read()  # 异步读取
                    else:
                        content = file.read()  # 同步读取
                else:
                    # 最后的同步读取方案
                    content = file.read()

                try:
                    text_content = content.decode('utf-8')
                except UnicodeDecodeError:
                    try:
                        text_content = content.decode('gbk')
                    except UnicodeDecodeError:
                        text_content = content.decode('latin-1', errors='replace')

                text_map[filename] = text_content.strip()
                print(f"成功处理文本文件: {file.filename}")
            except Exception as e:
                print(f"处理文本文件 {file.filename} 失败: {str(e)}")
                continue

        return text_map

    @classmethod
    async def _process_image_files(cls, image_files: List[Any], text_map: Dict[str, str],
                                   errors: List[str], target_dir_str: str, task_id: str, total_image_tasks: int) -> \
    List[CharacterCreate]:
        """处理图片文件，生成角色对象列表，同时更新任务进度"""
        characters = []
        processed = 0

        for file in image_files:
            try:
                # 检查任务是否已被取消
                if task_id not in task_manager.tasks:
                    raise Exception(f"任务 {task_id} 已被取消")

                name = os.path.splitext(file.filename)[0]
                tag = text_map.get(name, name)
                image_path = await cls._save_image_file(file, target_dir_str)

                if not image_path or not await cls.file_exists(image_path):
                    err_msg = f"图片保存失败: {file.filename}"
                    errors.append(err_msg)
                else:
                    character = CharacterCreate(
                        name=name,
                        cn_name=name,
                        tag=tag,
                        image=image_path,
                        description=None,
                        rating=None,
                        favorite=False,
                        disliked=False
                    )
                    characters.append(character)
                    print(f"成功处理图片文件: {file.filename}")

                # 每处理完一个文件就更新进度
                processed += 1
                task_manager.update_progress(task_id, processed)

                # 短暂休眠以允许事件循环处理进度更新事件
                await asyncio.sleep(0.01)

            except Exception as e:
                err_msg = f"处理文件 {file.filename} 失败: {str(e)}"
                errors.append(err_msg)
                print(f"{err_msg}, 详细信息: {traceback.format_exc()}")

                # 即使出错也更新进度，确保进度能继续推进
                processed += 1
                task_manager.update_progress(task_id, processed)
                continue

        return characters

    @classmethod
    async def _save_image_file(cls, file: Any, target_dir_str: str) -> str:
        """保存图片文件到指定目录并根据原始文件名转换为webp格式"""
        try:
            # 验证目标目录参数
            if not target_dir_str or not isinstance(target_dir_str, str):
                raise ValueError("目标路径不存在或无效")

            # 解码URL编码的路径
            decoded_path = unquote(target_dir_str)

            # 处理不同格式的路径，确保它是目录
            if decoded_path.startswith("file://"):
                local_path = decoded_path.replace("file://", "")
                if local_path.startswith("/"):
                    local_path = local_path[1:]
                local_path = local_path.replace("/", "\\")  # 适配Windows路径
                target_dir = Path(local_path).resolve()
            else:
                # 处理绝对路径，确保它是目录
                target_dir = Path(decoded_path).resolve()

            # 从原始文件名提取名称（不含扩展名）
            original_name = os.path.splitext(file.filename)[0]
            # 生成目标文件名（原始名称 + .webp）
            target_filename = f"{original_name}.webp"
            # 组合目录和文件名得到完整路径
            target_path = target_dir / target_filename

            # 确保父目录存在
            if not await cls.file_exists(str(target_dir)):
                loop = asyncio.get_event_loop()
                await loop.run_in_executor(
                    None,
                    lambda: os.makedirs(target_dir, exist_ok=True, mode=0o755)
                )

            # 生成临时文件路径（用于原子操作）
            temp_file_name = f"{original_name}.tmp-{os.urandom(8).hex()}.webp"
            temp_path = target_dir / temp_file_name

            try:
                # 读取文件内容
                if hasattr(file, 'file'):
                    content = file.file.read()  # 同步读取
                elif hasattr(file, 'read'):
                    if inspect.iscoroutinefunction(file.read):
                        content = await file.read()  # 异步读取
                    else:
                        content = file.read()  # 同步读取
                else:
                    content = file.read()  # 最后的读取方案

                if not content:
                    raise ValueError("文件内容为空")

                # 转换图片为webp格式并保存到临时文件
                loop = asyncio.get_event_loop()
                await loop.run_in_executor(
                    None,
                    cls._convert_image,
                    content,
                    str(temp_path)
                )

                # 原子性重命名临时文件到目标路径
                await loop.run_in_executor(
                    None,
                    lambda: os.replace(str(temp_path), str(target_path))
                )

                abs_path = str(target_path.resolve())
                print(f"图片保存成功: {abs_path}")
                return abs_path

            except Exception as conversion_error:
                # 清理临时文件
                if await cls.file_exists(str(temp_path)):
                    try:
                        await loop.run_in_executor(
                            None,
                            lambda: os.unlink(str(temp_path))
                        )
                    except Exception as cleanup_error:
                        print(f"清理临时文件失败: {str(cleanup_error)}")
                raise ValueError(f"图片处理失败: {str(conversion_error)}")

        except Exception as e:
            print(f"保存图片失败: {str(e)}, 详细信息: {traceback.format_exc()}")
            raise

    @staticmethod
    def _convert_image(image_data: bytes, output_path: str):
        """转换图片为WebP格式（同步方法，在 executor 中运行）"""
        try:
            with Image.open(BytesIO(image_data)) as img:
                # 处理透明背景
                if img.mode in ('RGBA', 'LA'):
                    # 创建白色背景
                    background = Image.new(img.mode[:-1], img.size, (255, 255, 255))
                    # 粘贴带有透明度的图片
                    background.paste(img, img.split()[-1])
                    img = background
                elif img.mode == 'P':
                    # 处理索引色模式
                    img = img.convert('RGB')

                # 保存为WebP格式，质量80（平衡质量和文件大小）
                img.save(output_path, 'WebP', quality=80)
        except Exception as e:
            raise ValueError(f"图片转换错误: {str(e)}")


    @staticmethod
    async def file_exists(file_path: Path | str) -> bool:
        """优化后的异步检查文件是否存在（兼容str和Path类型）"""
        try:
            # 先将输入转换为Path（无论原始类型是str还是Path）
            path = Path(file_path) if isinstance(file_path, str) else file_path
            return await asyncio.to_thread(path.exists)
        except Exception:
            # 发生异常时默认返回不存在（例如权限不足）
            return False

    @classmethod
    async def bulk_add_characters(cls, characters: List[CharacterCreate]) -> int:
        if not characters:
            return 0

        with db_manager.get_connection() as conn:
            try:
                insert_data = []
                for char in characters:
                    insert_data.append([
                        char.name,
                        char.cn_name,
                        char.tag
                    ])

                cursor = conn.executemany("""
                        INSERT OR IGNORE INTO character (
                            name, cn_name, tag
                        ) VALUES (?, ?, ?)
                    """, insert_data)

                conn.commit()
                print(f"成功批量插入 {cursor.rowcount} 个角色")
                return cursor.rowcount

            except sqlite3.IntegrityError as e:
                conn.rollback()
                error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
                raise ValueError(f"数据完整性错误: {error_msg}")
            except Exception as e:
                conn.rollback()
                error_msg = str(e).replace('\n', ' ').replace('\r', ' ')
                raise ValueError(f"插入数据失败: {error_msg}")


async def handle_get_random_character(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        rating_range = RatingRange(**data)
        result = await CharacterService.get_random_character(rating_range.rating)
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_adjacent_character(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        req_data = AdjacentRequest(**data)
        result = await CharacterService.get_adjacent_character(
            req_data.current_name,
            req_data.direction,
            req_data.rating
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_character(request: web.Request) -> web.Response:
    data = await request.json()
    if not data.get('name'):
        return web.json_response({"error": "人物名是必须的"}, status=400)

    try:
        result = await CharacterService.delete_character(data['name'])
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)

async def handle_delete_disliked_character(request: web.Request) -> web.Response:
    try:
        result = await CharacterService.delete_disliked_character()
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_update_character(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        result = await CharacterService.update_character(data)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_all_characters(request: web.Request) -> web.Response:
    try:
        result = await CharacterService.get_all_characters()
        return web.json_response(result)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_add_character(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        character = CharacterCreate(**data)
        result = await CharacterService.add_character(character)
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_batch_add_character(request: web.Request) -> web.Response:
    try:
        data = await request.json()
        character = CharacterCreate(**data)
        result = await CharacterService.add_character(character)
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_character_paged(request: web.Request) -> web.Response:
    try:
        # 解析查询参数
        page = int(request.query.get('page', 1))
        page_size = int(request.query.get('page_size', 10))
        name = request.query.get('name')
        tag = request.query.get('tag')
        cn_name = request.query.get('cn_name')
        description = request.query.get('description')
        favorite = request.query.get('favorite')
        disliked = request.query.get('disliked')
        rating_min = float(request.query.get('rating_min')) if request.query.get('rating_min') else None
        rating_max = float(request.query.get('rating_max')) if request.query.get('rating_max') else None
        sort_field = request.query.get('sort_field', 'name')
        sort_order = request.query.get('sort_order', 'ASC')

        # 验证分页参数
        if page < 1 or page_size < 1 or page_size > 100:
            return web.json_response({
                "error": "页码必须大于0，页大小必须在1-100之间"
            }, status=400)

        result = await CharacterService.get_character_paged(
            page, page_size, name, tag, cn_name, description,
            favorite, disliked, rating_min, rating_max,
            sort_field, sort_order
        )
        return web.json_response(result)
    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_get_character_detail(request: web.Request) -> web.Response:
    name = request.query.get('name')
    if not name:
        return web.json_response({"error": "角色名称必填"}, status=400)

    try:
        result = await CharacterService.get_character_detail(name)
        return web.json_response(result)
    except web.HTTPError as e:
        return web.json_response({"error": e.reason}, status=e.status)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)


async def handle_delete_by_filter(request: web.Request) -> web.Response:
    """按筛选条件删除所有符合条件的记录"""
    try:
        # 解析查询参数（与get_character_paged相同）
        name = request.query.get('name')
        tag = request.query.get('tag')
        cn_name = request.query.get('cn_name')
        description = request.query.get('description')
        favorite = request.query.get('favorite')
        disliked = request.query.get('disliked')
        rating_min = float(request.query.get('rating_min')) if request.query.get('rating_min') else None
        rating_max = float(request.query.get('rating_max')) if request.query.get('rating_max') else None

        # 构建查询条件
        where: List[str] = []
        params: List[Any] = []
        print("name:\t"+name)
        if name:
            where.append('name LIKE ?')
            params.append(f'%{name}%')
        if cn_name:
            where.append('cn_name LIKE ?')
            params.append(f'%{cn_name}%')
        if tag:
            where.append('tag = ?')
            params.append(tag)
        if description:
            where.append('description LIKE ?')
            params.append(f'%{description}%')

        if favorite is not None and favorite != '':
            where.append('favorite = ?')
            params.append(favorite)
        if disliked is not None and disliked != '':
            where.append('disliked = ?')
            params.append(disliked)
        if rating_min is not None:
            where.append('rating >= ?')
            params.append(rating_min)
        if rating_max is not None:
            where.append('rating <= ?')
            params.append(rating_max)

        where_clause = f'WHERE {" AND ".join(where)}' if where else ''
        # 执行删除操作
        with db_manager.get_connection() as conn:
            # 先查询符合条件的记录数量（用于返回信息）
            count_cursor = conn.execute(
                f'SELECT COUNT(*) AS total FROM character {where_clause}',
                params
            )
            total = count_cursor.fetchone()[0]

            if total == 0:
                return web.json_response({
                    "success": True,
                    "message": "未找到符合条件的记录",
                    "deleted_count": 0
                })

            # 执行删除
            delete_cursor = conn.execute(
                f'DELETE FROM character {where_clause}',
                params
            )
            conn.commit()

            return web.json_response({
                "success": True,
                "message": f"成功删除 {delete_cursor.rowcount} 条记录",
                "deleted_count": delete_cursor.rowcount
            })

    except ValueError as e:
        return web.json_response({"error": str(e)}, status=400)
    except Exception as e:
        return web.json_response({"error": str(e)}, status=500)

# 路由配置
routes = [
    web.post("/sql/get_random_character", handle_get_random_character),
    web.post("/sql/get_adjacent_character", handle_get_adjacent_character),
    web.post("/sql/delete_character", handle_delete_character),
    web.post("/sql/delete_disliked_character", handle_delete_disliked_character),
    web.delete("/sql/delete_characters_by_filter", handle_delete_by_filter),
    web.put("/sql/update_character", handle_update_character),
    web.get("/sql/get_character", handle_get_all_characters),
    web.post("/sql/add_character", handle_add_character),
    web.post("/sql/batch_add_character", CharacterService.batch_add_character),
    web.get("/sql/get_character_paged", handle_get_character_paged),
    web.get("/sql/get_character_detail", handle_get_character_detail),
]
